/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.mobile.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import com.cmcc.zysoft.infocollection.model.News;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: NewsDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-8-12 上午11:32:11
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

@Repository
public class MNewsDao extends HibernateBaseDaoImpl<News, String> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取最新通知-通过该接口,显示用户未读的最新一条信息.
	 * @param userId 用户ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> getUnreadNews(String userId) {
		String sql = 
				" SELECT news.news_id AS newsId, news.title AS title, news.content AS content, news.publish_time AS publishTime, " +
				" (SELECT a.real_name FROM tb_c_system_user a WHERE a.user_id = news.publish_man AND a.del_flag = '0') AS publishMan, " +
				" news.state AS state, news.status AS 'status' FROM tb_b_news news " +
				" WHERE news.state = '1' AND news.status = '0' AND news.news_id NOT IN " +
				" ( SELECT a.news_id FROM tb_b_read_news a WHERE a.user_id = ? ) " +
				" ORDER BY news.publish_time DESC LIMIT 0, 1 ";
		return this.jdbcTemplate.queryForList(sql, userId);
	}
	
	/**
	 * 消息中心-通过该接口,用户可查看自己的未通过(passFlag=-1)信息和省公司下发的通知公告.
	 * @param usercode 手机号
	 * @param userId 用户ID
	 * @param page 页码
	 * @param pagesize 每页显示个数
	 * @return map
	 */
	public Map<String, Object> getNews(String usercode, String userId, int page, int pagesize) {
		Map<String, Object> map = new HashMap<String, Object>();
		int offset = (page - 1) * pagesize;
		String sql = 
				" SELECT a.news_id AS message_id, a.title AS title, a.content AS messageContent, " +
				" (SELECT c.user_name FROM tb_c_system_user c WHERE a.publish_man = c.user_id) AS editor, " +
				" DATE_FORMAT(a.publish_time, '%c月%e日') AS 'date', a.news_type AS 'type', a.publish_time AS publishTime " +
				" FROM tb_b_news a WHERE a.status = '0' " +
				" UNION ALL " +
				" SELECT b.message_id AS message_id, b.message_title AS title, b.message_content AS messageContent, " +
				" (SELECT c.user_name FROM tb_c_system_user c WHERE b.add_man = c.user_id) AS editor, " +
				" DATE_FORMAT(b.add_date, '%c月%e日') AS 'date', b.message_type AS 'type', b.add_date AS publishTime " +
				" FROM tb_b_message b LEFT JOIN tb_b_resource c ON b.message_key = c.resource_id " +
				" WHERE b.mobile = '" + usercode + "' AND c.pass_flag = '-1'" +
				" ORDER BY publishTime DESC LIMIT ?, ?";
		String countSql = 
				" SELECT SUM(num) AS total FROM ( " + 
				" SELECT COUNT(*) AS num FROM tb_b_news a WHERE a.status = '0'" + 
				" UNION ALL " + 
				" SELECT COUNT(*) AS num FROM tb_b_message b LEFT JOIN tb_b_resource c ON b.message_key = c.resource_id " + 
				" WHERE b.mobile = '" + usercode + "' AND c.pass_flag = '-1') tableName";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, offset, pagesize);
		int total = this.jdbcTemplate.queryForInt(countSql);
		String type = "";
		String messageId = "";
		String content = "";
		int read = 0;  //read 0-未读 ,1-已读
		for(Map<String, Object> listMap : list) {
			read = 0;  //设置初始化状态为:未读状态
			type = listMap.get("type").toString(); //类型:0-news,1-message
			messageId = listMap.get("message_id").toString();
			content = listMap.get("messageContent").toString();
			if("1".equals(type)) {
				if(this.isMessageRead(messageId, usercode)) {
					read = 1;
				}
				listMap.put("read_mark", read); //设置消息已读未读标志
			} else {
				if(this.isNewsRead(messageId, userId)) {
					read = 1;
				}
				listMap.put("read_mark", read); //设置通知已读未读标志
			}
			//去掉消息内容中的HTML标签
			if(StringUtils.hasText(content)) {
				String str = content.replace("&hellip;", "\u2026").replaceAll("<.+?>", "").replaceAll("\\s+", "");
				int len = str.length();
		        if (len > content.length()) {
		            str = str.substring(0, content.length());
		            str += "......";
		        }
		        content = str;
			} else {
				content = "";
			}
			//截取content内容
			if(StringUtils.hasText(content) && content.length() >= 20) {
				content = content.substring(0, 20) + "……";
			} else {
				content += "……";
			}
			listMap.put("content", content);
		}
		map.put("result", list);
		map.put("total", total);
		map.put("status", "1");
		return map;
	}
	
	/**
	 * 判断通知是否已读.
	 * @param newsId 通知ID
	 * @param userId 用户ID
	 * @return Boolean
	 */
	private Boolean isNewsRead(String newsId, String userId) {
		Boolean flag = false;
		String sql = "SELECT read_num FROM tb_b_read_news WHERE news_id = ? AND user_id = ?";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, newsId, userId);
		if(!list.isEmpty()) {
			flag = true;
		}
		return flag;
	}
	
	/**
	 * 判断消息是否已读.
	 * @param messageId 消息ID
	 * @param usercode 手机号
	 * @return Boolean
	 */
	private Boolean isMessageRead(String messageId, String usercode) {
		Boolean flag = false;
		String sql = "SELECT a.* FROM tb_b_message a WHERE a.message_id = ? AND a.mobile = ?";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, messageId, usercode);
		//是否已读(message_read_flag):0-未读 1-已读
		if(null != list && !list.isEmpty() && "1".equals(list.get(0).get("message_read_flag").toString())) {
			flag = true;
		}
		return flag;
	}
	
	/**
	 * 通过通知ID获取通知详细信息.
	 * @param newsId 通知ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> getNewsById(String newsId) {
		String sql = 
				" SELECT a.news_id AS newsId, a.title AS title, a.content AS content, a.publish_time AS publishTime, " +
				" (SELECT b.user_name FROM tb_c_system_user b WHERE a.publish_man = b.user_id) AS publishMan " +
				" FROM tb_b_news a WHERE a.news_id = ?";
		return this.jdbcTemplate.queryForList(sql, newsId);
	}
}